#coding=utf-8
from framework.db.mysql import BaseDb
from tornado import gen
from lib import log
import time
from lib import libtime


class Thumb(BaseDb):
    @gen.coroutine
    def listmonth(self):
        
        now = int(time.time())
        timeArray = time.localtime(now)
        nmonth = time.strftime("%Y-%m", timeArray)
        #月初
        timeArray1 = time.strptime(nmonth, "%Y-%m")
        startime=int(time.mktime(timeArray1))
        #月末
                
        timeArray2=list(timeArray1)
        timeArray2[1]=timeArray2[1]+1
        timeArray2=tuple(timeArray2)
         
        endtime=int(time.mktime(timeArray2))
        

        sql='''SELECT s.Fid,s.Fname,s.Fage,s.Favater,s.Fhire_date,s.Fdorm_id,d.Fdorm_name,count(t.Fcrew_id) AS sport FROM 
        tb_staff AS s LEFT JOIN tb_dorm AS d ON s.Fdorm_id=d.Fid LEFT JOIN 
        tb_thumb_log AS t ON t.Fcrew_id=s.Fid WHERE t.Fcreate_time>%s AND t.Fcreate_time <%s GROUP BY t.Fcrew_id HAVING sport >0'''

        sql_cnt='SELECT count(Fid) as count FROM tb_thumb_log WHERE Fcreate_time>%s AND Fcreate_time <%s'
        try:
            ret = yield self.executejson(sql,(startime,endtime))
            
            count=yield self.executejson(sql_cnt,(startime,endtime))
            print count[0]['count']
            print ret
            for x in ret:
                #str2=(x['sport']/count[0])
                #str2=int(x['sport'])/count[0]
                
                a=float(x['sport'])
                b=int(count[0]['count'])
                x['sport']="%.2f%%" % (a/b*100)
                x['count']=int(a)
            resp = {'ret': ret,'count':count[0],'nmonth':nmonth}
           
        except Exception, e:
            log.log_exception(self.__class__.__name__, e)
            resp = '-1'
        raise gen.Return(resp)


    @gen.coroutine
    def monthsr(self,Fcreate_time,Ftype_id):

        if Fcreate_time:
            timeArray1 = time.strptime(Fcreate_time, "%Y-%m")
            starttime=int(time.mktime(timeArray1))
          
            timeArray2=list(timeArray1)
            timeArray2[1]=timeArray2[1]+1
            timeArray2=tuple(timeArray2)
         
            endtime=int(time.mktime(timeArray2))
        else:
            starttime=0
            endtime=int(time.time())
        #当前月份 默认使用
        now = int(time.time())
        timeArray = time.localtime(now)
        nmonth = time.strftime("%Y-%m", timeArray)


        sql='''SELECT s.Fid,s.Fname,s.Fage,s.Favater,s.Fhire_date,s.Fdorm_id,d.Fdorm_name,count(t.Fcrew_id) AS sport FROM 
        tb_staff AS s LEFT JOIN tb_dorm AS d ON s.Fdorm_id=d.Fid LEFT JOIN 
        tb_thumb_log AS t ON t.Fcrew_id=s.Fid WHERE t.Fcreate_time>%s AND t.Fcreate_time <%s AND t.Ftype_id=%s GROUP BY t.Fcrew_id HAVING sport >0 '''

        sql_cnt='SELECT count(Fid) as count FROM tb_thumb_log WHERE Fcreate_time >%s AND Fcreate_time<%s AND Ftype_id=%s '
        try:
            ret = yield self.executejson(sql,(starttime,endtime,Ftype_id))
            
            count=yield self.executejson(sql_cnt,(starttime,endtime,Ftype_id))

            for x in ret:
                #str2=(x['sport']/count[0])
                #str2=int(x['sport'])/count[0]
                
                a=float(x['sport'])
                b=int(count[0]['count'])
                x['sport']="%.2f%%" % (a/b*100)
                x['count']=int(a)
            resp = {'ret': ret,'count':count[0],'nmonth':nmonth}
            
        except Exception, e:
            log.log_exception(self.__class__.__name__, e)
            resp = '-1'
        raise gen.Return(resp)
        


    @gen.coroutine
    def listyear(self):

        now = int(time.time())
        timeArray = time.localtime(now)
        nyear = time.strftime("%Y", timeArray)

        #年初
        timeArray1 = time.strptime(nyear, "%Y")
        startime=int(time.mktime(timeArray1))
        #年末
                
        timeArray2=list(timeArray1)

        timeArray2[0]=timeArray2[0]+1
        timeArray2=tuple(timeArray2)
         
        endtime=int(time.mktime(timeArray2))
        
        sql='''SELECT s.Fid,s.Fname,s.Fage,s.Favater,s.Fhire_date,s.Fdorm_id,d.Fdorm_name,count(t.Fcrew_id) AS sport FROM 
        tb_staff AS s LEFT JOIN tb_dorm AS d ON s.Fdorm_id=d.Fid LEFT JOIN 
        tb_thumb_log AS t ON t.Fcrew_id=s.Fid WHERE t.Fcreate_time>%s AND t.Fcreate_time <%s  GROUP BY t.Fcrew_id HAVING sport >0'''

        sql_cnt='SELECT count(Fid) as count FROM tb_thumb_log WHERE Fcreate_time>%s AND Fcreate_time <%s'
        try:
            ret = yield self.executejson(sql,(startime,endtime))
            
            count=yield self.executejson(sql_cnt,(startime,endtime))

            for x in ret:
                #str2=(x['sport']/count[0])
                #str2=int(x['sport'])/count[0]
                
                a=float(x['sport'])
                b=int(count[0]['count'])
                x['sport']="%.2f%%" % (a/b*100)
                x['count']=int(a)
            resp = {'ret': ret,'count':count[0],'nyear':nyear}
            print resp
        except Exception, e:
            log.log_exception(self.__class__.__name__, e)
            resp = '-1'
        raise gen.Return(resp)


    @gen.coroutine
    def yearsr(self,Fcreate_time,Ftype_id):

        now = int(time.time())
        timeArray = time.localtime(now)
        nyear = time.strftime("%Y", timeArray)
        if Fcreate_time:
            timeArray1 = time.strptime(Fcreate_time, "%Y")
            starttime=int(time.mktime(timeArray1))
          
            timeArray2=list(timeArray1)
            timeArray2[0]=timeArray2[0]+1
            timeArray2=tuple(timeArray2)
         
            endtime=int(time.mktime(timeArray2))
        else:
            starttime=0
            endtime=int(time.time())


        sql='''SELECT s.Fid,s.Fname,s.Fage,s.Favater,s.Fhire_date,s.Fdorm_id,d.Fdorm_name,count(t.Fcrew_id) AS sport FROM 
        tb_staff AS s LEFT JOIN tb_dorm AS d ON s.Fdorm_id=d.Fid LEFT JOIN 
        tb_thumb_log AS t ON t.Fcrew_id=s.Fid WHERE t.Fcreate_time>%s AND t.Fcreate_time <%s AND t.Ftype_id=%s GROUP BY t.Fcrew_id HAVING sport >0 '''

        sql_cnt='SELECT count(Fid) as count FROM tb_thumb_log WHERE Fcreate_time >%s AND Fcreate_time<%s AND Ftype_id=%s '
        try:
            ret = yield self.executejson(sql,(starttime,endtime,Ftype_id))
            
            count=yield self.executejson(sql_cnt,(starttime,endtime,Ftype_id))

            for x in ret:
                #str2=(x['sport']/count[0])
                #str2=int(x['sport'])/count[0]
                
                a=float(x['sport'])
                b=int(count[0]['count'])
                x['sport']="%.2f%%" % (a/b*100)
                x['count']=int(a)
            resp = {'ret': ret,'count':count[0],'nyear':nyear}
            
        except Exception, e:
            log.log_exception(self.__class__.__name__, e)
            resp = '-1'
        raise gen.Return(resp)


  